package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import util.JDBCUtil;
import ex.DatabaseException;

public class RatingDAO {

	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String SELECT_RATING_BY_UID1_UID2 = "SELECT rating FROM ratings WHERE uid1 = ? AND uid2 = ?";
	private static final String INSERT_USER_RATING = "INSERT INTO ratings (uid1, uid2, rating) values (?, ? ,?)";
	private static final String INSERT_MOVIE_RATING = "INSERT INTO ratings (uid1, mid2, rating) values (?, ? ,?)";
	private static final String SELECT_RATING_BY_UID1_MID2 = "SELECT rating FROM ratings WHERE uid1 = ? AND mid2 = ?";
	

	public short selectRatingByUid1Uid2(long uid1, long uid2) throws DatabaseException {
		conn = JDBCUtil.dequeueConn();

		short rating = 0;
	
		try {
			stmt = conn.prepareStatement(SELECT_RATING_BY_UID1_UID2);
			stmt.setLong(1, uid1);
			stmt.setLong(2, uid2);
			rs = stmt.executeQuery();
			if (rs.next()) {
				rating = (short) rs.getInt("rating");
			}	
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving rating information by uid1 and uid2! "
							+ e.getMessage());
		}
	
		return rating;
	}
	
	public short selectRatingByUid1Mid2(long uid1, long mid) throws DatabaseException {
		conn = JDBCUtil.dequeueConn();

		short rating = 0;
	
		try {
			stmt = conn.prepareStatement(SELECT_RATING_BY_UID1_MID2);
			stmt.setLong(1, uid1);
			stmt.setLong(2, mid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				rating = (short) rs.getInt("rating");
			}	
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving rating information by uid1 and mid2! "
							+ e.getMessage());
		}
	
		return rating;
	}

	public void insertUserRating(long uid1, long uid2, int rating) throws DatabaseException {
	
		

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(INSERT_USER_RATING);
			stmt.setLong(1, uid1);
			stmt.setLong(2, uid2);
			stmt.setInt(3, rating);

			stmt.execute();

			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting rating information! "
							+ e.getMessage());
	}
}

public void insertMovieRating(long uid1, long mid2, int rating) throws DatabaseException {
	
		

		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(INSERT_MOVIE_RATING);
			stmt.setLong(1, uid1);
			stmt.setLong(2, mid2);
			stmt.setInt(3, rating);

			stmt.execute();

			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting rating information! "
							+ e.getMessage());
	}
}
	
}